/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package brisollogic;

import java.io.FileNotFoundException;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 *laver databasen samt testbrugere
 * @author JMK
 */
public class CreateDb {

    public static void main(String[] args) throws SQLException {
        String dropDatabasesql = "USE master DROP database BrisolDB";
        String createDatabasesql = "USE master CREATE database BrisolDB";
        String createTableProductsql = "USE BrisolDB CREATE TABLE Product(ProductId INT IDENTITY(1,1) PRIMARY KEY, ModelName VARCHAR(50) NOT NULL, Specs VARCHAR(255) NOT NULL, Category VARCHAR(20) NOT NULL, Picture VARCHAR(40) NOT NULL)";
        String createTableUsersql = "USE BrisolDB CREATE TABLE Users(UserId INT IDENTITY(1,1) PRIMARY KEY, UserName VARCHAR(40) NOT NULL, Email VARCHAR(40) NOT NULL, UserStatus VARCHAR(10) NOT NULL, Password VARCHAR(100) NOT NULL)";
        PreparedStatement pstmt = null;
        DatabaseConnection dal = DAL.INSTANCE;
        dropDatabase(dropDatabasesql, dal, pstmt);
        createDatabase(createDatabasesql, dal, pstmt);
        createTableProduct(createTableProductsql, dal, pstmt);
        createTableUsers(createTableUsersql, dal, pstmt);
        createTestUsers();
        createTestProducts();

    }

    public static void dropDatabase(String sql, DatabaseConnection dal, PreparedStatement pstmt) {
        try {
            pstmt = dal.getConnection().prepareStatement(sql);
            pstmt.executeUpdate();
        } catch (SQLException | ClassNotFoundException e) {
            System.out.println("Database could not be dropped! " + e.getMessage());
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                System.out.println("Could not close connection " + e.getMessage());
            }
        }

    }

    public static void createDatabase(String sql, DatabaseConnection dal, PreparedStatement pstmt) {
        try {
            pstmt = dal.getConnection().prepareStatement(sql);
            pstmt.executeUpdate();
        } catch (SQLException  | ClassNotFoundException e ) {
            System.out.println("Database could not be created! " + e.getMessage());
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                System.out.println("Could not close connection " + e.getMessage());
            }
        }
    }

    public static void createTableProduct(String sql, DatabaseConnection dal, PreparedStatement pstmt) {
        try {
            pstmt = dal.getConnection().prepareStatement(sql);
            pstmt.executeUpdate();
        } catch (SQLException | ClassNotFoundException e) {
            System.out.println("Table could not be created! " + e.getMessage());
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                System.out.println("Could not close connection " + e.getMessage());
            }
        }
    }

    public static void createTableUsers(String sql, DatabaseConnection dal, PreparedStatement pstmt) {
        try {
            pstmt = dal.getConnection().prepareStatement(sql);
            pstmt.executeUpdate();
        } catch (SQLException | ClassNotFoundException e) {
            System.out.println("Table could not be created! " + e.getMessage());
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                System.out.println("Could not close connection " + e.getMessage());
            }
        }
    }
    
    private static void createTestUsers() {
        User user;
        try {
            Logic logic = new Logic();
            user = new User.UserBuilder(UserCredentials.getInstanceHashPassword("testadmin@test.com", "Pa$$w0rd")).name("testadmin").status(User.Status.Admin).build();

            logic.insertUserIntoDb(user);

            user = new User.UserBuilder(UserCredentials.getInstanceHashPassword("testuser@test.com", "Pa$$w0rd")).name("testuser").status(User.Status.User).build();

            logic.insertUserIntoDb(user);
        } catch (SQLException | ClassNotFoundException e) {
            System.out.println(e.getMessage());
        }
    }

    private static void createTestProducts() {       
        try {
            Logic logic = new Logic();
            Product prod = new Product("3560x", "fast", Product.Category.Switch, "switch.png");

            logic.insertProductIntoDb(prod);

            prod = new Product("7609", "very fast", Product.Category.Router, "Router.png");

            logic.insertProductIntoDb(prod);

            prod = new Product("ASA5505", "Many connections", Product.Category.Firewall, "Firewall.png");

            logic.insertProductIntoDb(prod);
        } catch (SQLException | ClassNotFoundException e) {
            System.out.println(e.getMessage());
        }
    }
}
